package com.api.gbhg.liuwenbo.version2.api;

import com.alibaba.fastjson.JSON;
import com.api.gbhg.liuwenbo.version2.pojo.BudgetSectionIncome2;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.http.HttpServletResponse;
import javax.ws.rs.POST;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;
import javax.ws.rs.core.Context;
import javax.ws.rs.core.MediaType;
import java.io.IOException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Description 行政合计导出报表
 * @Author lwb
 * @Date 7.27
 */
@Path("gb/Administration")
public class AdministrationExportApi {

    AdministrationApi bsi = new AdministrationApi();

    private int thisIndex = 3;
    private HSSFCellStyle fontStyle = null;//正常字体大小
    private HSSFCellStyle fontCenterStyle = null;//字体大小跟居中
    private HSSFCellStyle cellStyle = null;//字体大小跟居中
    private BigDecimal snxj = new BigDecimal("0");//上年小计
    private BigDecimal bnxj = new BigDecimal("0");//本年小计
    private BigDecimal zj = new BigDecimal("0");//总计
    private BigDecimal snjzysapxj = new BigDecimal("0");//上年结转预算安排
    private BigDecimal bnsrysapxj = new BigDecimal("0");//本年收入预算安排
    private BigDecimal hj1zj = new BigDecimal("0");//合计1
    private BigDecimal snjzwapxj = new BigDecimal("0");//上年结转未安排
    private BigDecimal bnsrwapxj = new BigDecimal("0");//本年收入未安排
    private BigDecimal hj2zj = new BigDecimal("0");//合计2总计
    private BigDecimal snjzyszcxj = new BigDecimal("0");//上年结转预算支出
    private BigDecimal bnsryszcxj = new BigDecimal("0");//本年收入预算支出
    private BigDecimal hj3zj = new BigDecimal("0");//合计3总计

    private BigDecimal jssnxj = new BigDecimal("0");//缉私上年小计
    private BigDecimal jsbnxj = new BigDecimal("0");//缉私本年小计
    private BigDecimal jszj = new BigDecimal("0");//总计
    private BigDecimal jssnjzysapxj = new BigDecimal("0");//上年结转预算安排
    private BigDecimal jsbnsrysapxj = new BigDecimal("0");//本年收入预算安排
    private BigDecimal jshj1zj = new BigDecimal("0");//合计1
    private BigDecimal jssnjzwapxj = new BigDecimal("0");//上年结转未安排
    private BigDecimal jsbnsrwapxj = new BigDecimal("0");//本年收入未安排
    private BigDecimal jshj2zj = new BigDecimal("0");//合计2总计
    private BigDecimal jssnjzyszcxj = new BigDecimal("0");//上年结转预算支出
    private BigDecimal jsbnsryszcxj = new BigDecimal("0");//本年收入预算支出
    private BigDecimal jshj3zj = new BigDecimal("0");//合计3总计
    private BigDecimal jssjsrzj = new BigDecimal("0");//实际收入总计

    private Map<String,Object> msgMap ;

    @POST
    @Path("/exportExcel")
    @Produces(MediaType.APPLICATION_JSON)
    public String exportExcel(@Context HttpServletResponse response, Map<String,Object> param){
        msgMap = new HashMap<>();
        if (param == null ||!param.containsKey("nf") ||!param.containsKey("jz")){
            msgMap.put("code","500");
            msgMap.put("msg","缺少参数 请检查参数是否完整！");
            msgMap.put("data","");
            return JSON.toJSONString(msgMap);
        }

        Map<String, Object> paramFormat = bsi.paramFormat(param);
        String s = downLoadExcel(response, paramFormat);

        return s;
    }

    /**
     * 浏览器导出
     * @param response
     * @param param
     */
    public String downLoadExcel(HttpServletResponse response,Map<String,Object> param)  {
        param.put("dw", bsi.getdw());
        List<BudgetSectionIncome2> czbksrData1 = bsi.getCzbksrData(param);
        param.put("dw",29);
        List<BudgetSectionIncome2> czbksrData2 = bsi.getCzbksrData(param);

        try {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet("预算科收入预算表");


            //新增数据行，并且设置单元格数据
            HSSFRow row = sheet.createRow(0);
            //定义单元格风格
            cellStyle = workbook.createCellStyle();
            //设置水平居中
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            //设置垂直居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            //设置字体
            HSSFFont font = workbook.createFont();
            //设置字体名称
//            font.setFontName("华文行楷");
            //设置字号
            font.setFontHeightInPoints((short)11);
            //设置是否为斜体
            font.setItalic(false);
            //设置是否加粗
            font.setBold(true);

            //设置字体颜色
            font.setColor(IndexedColors.BLACK.index);
            //设置行的高度
            cellStyle.setFont(font);
            row.setHeightInPoints(20);
            //设置默认的列宽度
            sheet.setDefaultColumnWidth(9);
            sheet.setColumnWidth(0,700*10);
            sheet.setColumnWidth(1,1200*10);
            sheet.setColumnWidth(2,500*10);
            sheet.setColumnWidth(3,500*10);
            sheet.setColumnWidth(4,500*10);
            sheet.setColumnWidth(5,500*10);
            sheet.setColumnWidth(6,550*10);
            sheet.setColumnWidth(7,500*10);
            sheet.setColumnWidth(8,500*10);
            sheet.setColumnWidth(9,500*10);
            sheet.setColumnWidth(10,500*10);
            sheet.setColumnWidth(11,500*10);
            sheet.setColumnWidth(12,500*10);
            sheet.setColumnWidth(13,500*10);
            sheet.setColumnWidth(14,500*10);
            sheet.setColumnWidth(15,500*10);
            sheet.setColumnWidth(16,600*10);
            sheet.setColumnWidth(17,600*10);
            sheet.setColumnWidth(18,500*10);
            sheet.setColumnWidth(19,500*10);
            sheet.setColumnWidth(20,500*10);

            //合并 四个参数（分别为起始行，结束行，起始列，结束列）
            CellRangeAddress region = new CellRangeAddress(0, 0, 0, 7);
            sheet.addMergedRegion(region);

            //添加excel表头第一行
            HSSFCell cellBt = row.createCell(0);
            cellBt.setCellStyle(cellStyle);
            HSSFRichTextString bt = new HSSFRichTextString(LocalDate.now().getYear()+"年财政拨款部门预算收支情况表（一）");
            cellBt.setCellValue(bt);

            HSSFRow rowbt1 = sheet.createRow(1);
            HSSFCell cellNf = rowbt1.createCell(0);
            cellNf.setCellStyle(cellStyle);
            HSSFRichTextString nf = new HSSFRichTextString("统计日期:"+LocalDate.now().getMonthValue()+"月"+LocalDate.now().getDayOfMonth());
            cellNf.setCellValue(nf);

            HSSFCell cell1_7wy = rowbt1.createCell(7);
            cell1_7wy.setCellStyle(cellStyle);
            cell1_7wy.setCellValue(new HSSFRichTextString("单位:万元"));
            //-----------表头添加结束

            //创建coloTitleStyle--------------------
            //设置背景颜色的style
            HSSFCellStyle coloTitleStyle = workbook.createCellStyle();
            coloTitleStyle.setFillForegroundColor(IndexedColors.AQUA.getIndex());
            coloTitleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            coloTitleStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            //设置字段style
            HSSFFont fontTitle = workbook.createFont();
            fontTitle.setFontHeightInPoints((short)11);
            coloTitleStyle.setFont(fontTitle);
            //end--------------------

            //创建字体大小无居中style----------------
            fontStyle = workbook.createCellStyle();
            fontStyle.setFont(fontTitle);
            //end----------------------

            //创建字体大小水平居中style----------------
            fontCenterStyle = workbook.createCellStyle();
            fontCenterStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            fontCenterStyle.setAlignment(HorizontalAlignment.CENTER);
            fontCenterStyle.setFont(fontTitle);
            //end----------------------
            //标题结束-----------------------------------


            //开始设置财政拨款收入标题1
            String[] headers1 = {"科目编码","科目名称","上年结转","本年收入","合计","上年结转预算安排","本年收入预算安排","合计","上年结转未安排",
                    "本年收入未安排","合计","上年结转预算支出","本年收入预算支出","支出合计","上年结转预算执行率","本年收入预算执行率","预算执行率合计"};

            HSSFRow row2 = sheet.createRow(2);
            for (int i = 0; i < headers1.length; i++) {
                HSSFCell cell = null;
                if (i==4){
                    cell = row2.createCell(i+2);
                }else if(i == 3){
                    cell = row2.createCell(i+1);
                }else if(i>4){
                    //下标已超前两位所以需要+2
                    cell = row2.createCell(i+2);
                }else{
                    cell = row2.createCell(i);
                }
                cell.setCellStyle(cellStyle);
                if (i==2 || i==3){
                    int j ;
                    if (i==3)
                        j = 4;
                    else
                        j = i;

                    row2.createCell(j+1);
                    cell.setCellValue(headers1[i]);
                    CellRangeAddress tmpregion = new CellRangeAddress(2, 2, j, j+1);
                    sheet.addMergedRegion(tmpregion);
                }else{
                    cell.setCellValue(headers1[i]);
                }
            }
            //---------------开始写入财政拨款收入1---------------------------------------
            String space = "      ";//空格
            int startIndex = 3;
            for (BudgetSectionIncome2 bean:czbksrData1) {
                HSSFRow czbksrRow = sheet.createRow(thisIndex);
                czbksrRow.setHeightInPoints(20);

                HSSFCell kmbm = czbksrRow.createCell(0);
                kmbm.setCellValue(bean.getName());

                HSSFCell kmdm = czbksrRow.createCell(1);
                kmdm.setCellValue(bean.getKmmc());

                czbksrRow.createCell(1).setCellValue(bean.getKmmc());

                czbksrRow.createCell(2).setCellValue(Double.parseDouble(bean.getSnjz()));
                czbksrRow.createCell(3);
                //合并行上年
                CellRangeAddress regionSn = new CellRangeAddress(thisIndex, thisIndex, 2, 3);
                sheet.addMergedRegion(regionSn);

                czbksrRow.createCell(4).setCellValue(Double.parseDouble(bean.getBnsr()));
                czbksrRow.createCell(5);
                //合并行本年
                CellRangeAddress regionBn = new CellRangeAddress(thisIndex, thisIndex, 4, 5);
                sheet.addMergedRegion(regionBn);

                czbksrRow.createCell(6).setCellValue(Double.parseDouble(bean.getHj()));

                czbksrRow.createCell(7).setCellValue(Double.parseDouble(bean.getSnjzysap()));

                czbksrRow.createCell(8).setCellValue(Double.parseDouble(bean.getBnsrysap()));

                czbksrRow.createCell(9).setCellValue(Double.parseDouble(bean.getHj1()));

                czbksrRow.createCell(10).setCellValue(Double.parseDouble(bean.getSnjzwap()));

                czbksrRow.createCell(11).setCellValue(Double.parseDouble(bean.getBnsrwap()));

                czbksrRow.createCell(12).setCellValue(Double.parseDouble(bean.getHj2()));

                czbksrRow.createCell(13).setCellValue(Double.parseDouble(bean.getSnjzyszc()));

                czbksrRow.createCell(14).setCellValue(Double.parseDouble(bean.getBnsryszc()));

                czbksrRow.createCell(15).setCellValue(Double.parseDouble(bean.getHj3()));

                czbksrRow.createCell(16).setCellValue(Double.parseDouble(bean.getSnjzyszxl()));

                czbksrRow.createCell(17).setCellValue(Double.parseDouble(bean.getBnsryszxl()));

                czbksrRow.createCell(18).setCellValue(Double.parseDouble(bean.getHj4()));

                thisIndex++;
                List<BudgetSectionIncome2> xj = bean.getXj();
                createZyczbksrXj1(sheet,xj,space);

                //计算小计 总计
                snxj = snxj.add(new BigDecimal(bean.getSnjz()));
                bnxj = bnxj.add(new BigDecimal(bean.getBnsr()));
                zj = zj.add(new BigDecimal(bean.getHj()));
                snjzysapxj = snjzysapxj.add(new BigDecimal(bean.getSnjzysap()));
                bnsrysapxj = bnsrysapxj.add(new BigDecimal(bean.getBnsrysap()));
                hj1zj = hj1zj.add(new BigDecimal(bean.getHj1()));
                snjzwapxj = snjzwapxj.add(new BigDecimal(bean.getSnjzwap()));
                bnsrwapxj = bnsrwapxj.add(new BigDecimal(bean.getBnsrwap()));
                hj2zj = hj2zj.add(new BigDecimal(bean.getHj2()));
                snjzyszcxj = snjzyszcxj.add(new BigDecimal(bean.getSnjzyszc()));
                bnsryszcxj = bnsryszcxj.add(new BigDecimal(bean.getBnsryszc()));
                hj3zj = hj3zj.add(new BigDecimal(bean.getHj3()));

                //设置表格中字体
                for (int k = 0; k < 19; k++) {
                    if (k == 1)
                        czbksrRow.getCell(k).setCellStyle(fontStyle);
                    else
                        czbksrRow.getCell(k).setCellStyle(fontCenterStyle);
                }

                if (startIndex != thisIndex -1){
                    CellRangeAddress regionKmbm = new CellRangeAddress(startIndex, thisIndex-1, 0, 0);
                    sheet.addMergedRegion(regionKmbm);
                }
                startIndex = thisIndex;
            }

            //-----------开始写入财政拨款收入2-----------------------------------------------------

            //开始设置财政拨款收入2标题
            HSSFRow row3 = sheet.createRow(thisIndex);
            HSSFCell row3_cellbt = row3.createCell(0);
            row3_cellbt.setCellStyle(cellStyle);
            row3_cellbt.setCellValue(new HSSFRichTextString("海关合计（含基建）"));

            thisIndex++;
            //开始写入财政拨款收入2数据------------------------------------
            startIndex = thisIndex;
            for (BudgetSectionIncome2 bean:czbksrData2) {
                HSSFRow czbksrRow = sheet.createRow(thisIndex);
                czbksrRow.setHeightInPoints(20);

                HSSFCell kmbm = czbksrRow.createCell(0);
                kmbm.setCellValue(bean.getName());

                HSSFCell kmdm = czbksrRow.createCell(1);
                kmdm.setCellValue(bean.getKmmc());

                czbksrRow.createCell(1).setCellValue(bean.getKmmc());

                czbksrRow.createCell(2).setCellValue(Double.parseDouble(bean.getSnjz()));
                czbksrRow.createCell(3);
                //合并行上年
                CellRangeAddress regionSn = new CellRangeAddress(thisIndex, thisIndex, 2, 3);
                sheet.addMergedRegion(regionSn);

                czbksrRow.createCell(4).setCellValue(Double.parseDouble(bean.getBnsr()));
                czbksrRow.createCell(5);
                //合并行本年
                CellRangeAddress regionBn = new CellRangeAddress(thisIndex, thisIndex, 4, 5);
                sheet.addMergedRegion(regionBn);

                czbksrRow.createCell(6).setCellValue(Double.parseDouble(bean.getHj()));

                czbksrRow.createCell(7).setCellValue(Double.parseDouble(bean.getSnjzysap()));

                czbksrRow.createCell(8).setCellValue(Double.parseDouble(bean.getBnsrysap()));

                czbksrRow.createCell(9).setCellValue(Double.parseDouble(bean.getHj1()));

                czbksrRow.createCell(10).setCellValue(Double.parseDouble(bean.getSnjzwap()));

                czbksrRow.createCell(11).setCellValue(Double.parseDouble(bean.getBnsrwap()));

                czbksrRow.createCell(12).setCellValue(Double.parseDouble(bean.getHj2()));

                czbksrRow.createCell(13).setCellValue(Double.parseDouble(bean.getSnjzyszc()));

                czbksrRow.createCell(14).setCellValue(Double.parseDouble(bean.getBnsryszc()));

                czbksrRow.createCell(15).setCellValue(Double.parseDouble(bean.getHj3()));

                czbksrRow.createCell(16).setCellValue(Double.parseDouble(bean.getSnjzyszxl()));

                czbksrRow.createCell(17).setCellValue(Double.parseDouble(bean.getBnsryszxl()));

                czbksrRow.createCell(18).setCellValue(Double.parseDouble(bean.getHj4()));

                thisIndex++;
                List<BudgetSectionIncome2> xj = bean.getXj();
                createZyczbksrXj2(sheet,xj,space);

                //计算小计 总计
                jssnxj = jssnxj.add(new BigDecimal(bean.getSnjz()));
                jsbnxj = jsbnxj.add(new BigDecimal(bean.getBnsr()));
                jszj = jszj.add(new BigDecimal(bean.getHj()));
                jssnjzysapxj = jssnjzysapxj.add(new BigDecimal(bean.getSnjzysap()));
                jsbnsrysapxj = jsbnsrysapxj.add(new BigDecimal(bean.getBnsrysap()));
                jshj1zj = jshj1zj.add(new BigDecimal(bean.getHj1()));
                jssnjzwapxj = jssnjzwapxj.add(new BigDecimal(bean.getSnjzwap()));
                jsbnsrwapxj = jsbnsrwapxj.add(new BigDecimal(bean.getBnsrwap()));
                jshj2zj = jshj2zj.add(new BigDecimal(bean.getHj2()));
                jssnjzyszcxj = jssnjzyszcxj.add(new BigDecimal(bean.getSnjzyszc()));
                jsbnsryszcxj = jsbnsryszcxj.add(new BigDecimal(bean.getBnsryszc()));
                jshj3zj = jshj3zj.add(new BigDecimal(bean.getHj3()));

                //设置表格中字体
                for (int k = 0; k < 19; k++) {
                    if (k == 1)
                        czbksrRow.getCell(k).setCellStyle(fontStyle);
                    else
                        czbksrRow.getCell(k).setCellStyle(fontCenterStyle);
                }

                if (startIndex != thisIndex -1){
                    CellRangeAddress regionKmbm = new CellRangeAddress(startIndex, thisIndex-1, 0, 0);
                    sheet.addMergedRegion(regionKmbm);
                }
                startIndex = thisIndex;
            }

            //-----------------------------开始写入合计
            HSSFRow jshjRow = sheet.createRow(thisIndex);//缉私合计
            HSSFCell jshjCell0 = jshjRow.createCell(0);
            jshjCell0.setCellStyle(cellStyle);
            jshjCell0.setCellValue(new HSSFRichTextString("缉私警察合计"));

            CellRangeAddress region1 = new CellRangeAddress(thisIndex, thisIndex, 0, 1);
            sheet.addMergedRegion(region1);

            HSSFCell jsCell2 = jshjRow.createCell(2);
            jshjRow.createCell(3);
            jsCell2.setCellValue("小计:"+jssnxj.toString());
            CellRangeAddress regFzysnxj = new CellRangeAddress(thisIndex, thisIndex, 2, 3);
            sheet.addMergedRegion(regFzysnxj);
            jsCell2.setCellStyle(cellStyle);

            HSSFCell jsCell4 = jshjRow.createCell(4);
            jshjRow.createCell(5);
            jsCell4.setCellValue("小计:"+jsbnxj.toString());
            CellRangeAddress regFzybnxj = new CellRangeAddress(thisIndex, thisIndex, 4, 5);
            sheet.addMergedRegion(regFzybnxj);
            jsCell4.setCellStyle(cellStyle);

            HSSFCell jsCell6= jshjRow.createCell(6);
            jsCell6.setCellValue("总计:"+jszj.toString());
            jsCell6.setCellStyle(cellStyle);

            HSSFCell jsCell7= jshjRow.createCell(7);
            jsCell7.setCellValue("小计:"+jssnjzwapxj.toString());
            jsCell7.setCellStyle(cellStyle);

            HSSFCell jsCell8= jshjRow.createCell(8);
            jsCell8.setCellValue("小计:"+jsbnsrwapxj.toString());
            jsCell8.setCellStyle(cellStyle);

            HSSFCell jsCell9= jshjRow.createCell(9);
            jsCell9.setCellValue("合计:"+jshj1zj.toString());
            jsCell9.setCellStyle(cellStyle);

            HSSFCell jsCell10= jshjRow.createCell(10);
            jsCell10.setCellValue("小计:"+jssnjzwapxj.toString());
            jsCell10.setCellStyle(cellStyle);

            HSSFCell jsCell11= jshjRow.createCell(11);
            jsCell11.setCellValue("小计:"+jsbnsrwapxj.toString());
            jsCell11.setCellStyle(cellStyle);

            HSSFCell jsCell12= jshjRow.createCell(12);
            jsCell12.setCellValue("合计:"+jshj2zj.toString());
            jsCell12.setCellStyle(cellStyle);

            HSSFCell jsCell13= jshjRow.createCell(13);
            jsCell13.setCellValue("小计:"+jssnjzyszcxj.toString());
            jsCell13.setCellStyle(cellStyle);

            HSSFCell jsCell14= jshjRow.createCell(14);
            jsCell14.setCellValue("小计:"+jsbnsryszcxj.toString());
            jsCell14.setCellStyle(cellStyle);

            HSSFCell jsCell15= jshjRow.createCell(15);
            jsCell15.setCellValue("合计:"+jshj3zj.toString());
            jsCell15.setCellStyle(cellStyle);

            HSSFCell jsCell19= jshjRow.createCell(19);
            jsCell19.setCellValue("合计:"+jssjsrzj.toString());
            jsCell19.setCellStyle(cellStyle);

            thisIndex++;
            HSSFRow hgzjRow = sheet.createRow(thisIndex);//缉私合计
            HSSFCell hgzjCell0 = hgzjRow.createCell(0);
            hgzjCell0.setCellStyle(cellStyle);
            hgzjCell0.setCellValue(new HSSFRichTextString("拱北海关总计"));

            CellRangeAddress region2 = new CellRangeAddress(thisIndex, thisIndex, 0, 1);
            sheet.addMergedRegion(region2);

            HSSFCell zyCell2 = hgzjRow.createCell(2);
            hgzjRow.createCell(3);
            zyCell2.setCellValue("小计:"+ snxj.toString());
            CellRangeAddress regZysnxj = new CellRangeAddress(thisIndex, thisIndex, 2, 3);
            sheet.addMergedRegion(regZysnxj);
            zyCell2.setCellStyle(cellStyle);

            HSSFCell zyCell4 = hgzjRow.createCell(4);
            hgzjRow.createCell(5);
            zyCell4.setCellValue("小计:"+ bnxj.toString());
            CellRangeAddress regZybnxj = new CellRangeAddress(thisIndex, thisIndex, 4, 5);
            sheet.addMergedRegion(regZybnxj);
            zyCell4.setCellStyle(cellStyle);

            HSSFCell zyCell6= hgzjRow.createCell(6);
            zyCell6.setCellValue("总计:"+ zj.toString());
            zyCell6.setCellStyle(cellStyle);

            HSSFCell zyCell7= hgzjRow.createCell(7);
            zyCell7.setCellValue("小计:"+snjzysapxj.toString());
            zyCell7.setCellStyle(cellStyle);

            HSSFCell zyCell8= hgzjRow.createCell(8);
            zyCell8.setCellValue("小计:"+bnsrysapxj.toString());
            zyCell8.setCellStyle(cellStyle);

            HSSFCell zyCell9= hgzjRow.createCell(9);
            zyCell9.setCellValue("总计:"+hj1zj.toString());
            zyCell9.setCellStyle(cellStyle);

            HSSFCell zyCell10= hgzjRow.createCell(10);
            zyCell10.setCellValue("小计:"+snjzwapxj.toString());
            zyCell10.setCellStyle(cellStyle);

            HSSFCell zyCell11= hgzjRow.createCell(11);
            zyCell11.setCellValue("小计:"+bnsrwapxj.toString());
            zyCell11.setCellStyle(cellStyle);

            HSSFCell zyCell12= hgzjRow.createCell(12);
            zyCell12.setCellValue("总计:"+hj2zj.toString());
            zyCell12.setCellStyle(cellStyle);

            HSSFCell zyCell13= hgzjRow.createCell(13);
            zyCell13.setCellValue("小计:"+snjzyszcxj.toString());
            zyCell13.setCellStyle(cellStyle);

            HSSFCell zyCell14= hgzjRow.createCell(14);
            zyCell14.setCellValue("小计:"+bnsryszcxj.toString());
            zyCell14.setCellStyle(cellStyle);

            HSSFCell zyCell15= hgzjRow.createCell(15);
            zyCell15.setCellValue("总计:"+hj3zj.toString());
            zyCell15.setCellStyle(cellStyle);


            //设置要导出的文件的名字
            String downFilename  = "info"  + ".xls";
            //服务器发给浏览器的数据是使用UTF-8编码
            response.setContentType("application/octet-stream ; charset=ISO-8859-1 ");

            //设置浏览器的编码为UTF-8
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(downFilename,"UTF-8"));

            response.flushBuffer();
            workbook.write(response.getOutputStream());

            msgMap.put("code","200");
            msgMap.put("msg","");
            msgMap.put("data","");
        } catch (IOException e) {
            e.printStackTrace();
            msgMap.put("code","500");
            msgMap.put("msg","程序异常，请联系管理员！");
            msgMap.put("data","");
        }
        return JSON.toJSONString(msgMap);
    }

    /**
     * 创建财政拨款收入下级
     * @param sheet
     * @param Data
     * @param space
     */
    public void createZyczbksrXj2(HSSFSheet sheet, List<BudgetSectionIncome2> Data, String space){
        if (Data == null){
            return ;
        }
        for (BudgetSectionIncome2 bean:Data) {
            HSSFRow czbksrRow = sheet.createRow(thisIndex);
            czbksrRow.setHeightInPoints(20);

            HSSFCell kmdm = czbksrRow.createCell(1);
            kmdm.setCellValue(bean.getKmmc());
            czbksrRow.createCell(1).setCellValue(space+bean.getKmmc());

            czbksrRow.createCell(2).setCellValue(Double.parseDouble(bean.getSnjz()));
            czbksrRow.createCell(3);
            //合并行上年
            CellRangeAddress regionSn = new CellRangeAddress(thisIndex, thisIndex, 2, 3);
            sheet.addMergedRegion(regionSn);

            czbksrRow.createCell(4).setCellValue(Double.parseDouble(bean.getBnsr()));
            czbksrRow.createCell(5);
            //合并行本年
            CellRangeAddress regionBn = new CellRangeAddress(thisIndex, thisIndex, 4, 5);
            sheet.addMergedRegion(regionBn);


            czbksrRow.createCell(6).setCellValue(Double.parseDouble(bean.getHj()));

            czbksrRow.createCell(7).setCellValue(Double.parseDouble(bean.getSnjzysap()));

            czbksrRow.createCell(8).setCellValue(Double.parseDouble(bean.getBnsrysap()));

            czbksrRow.createCell(9).setCellValue(Double.parseDouble(bean.getHj1()));

            czbksrRow.createCell(10).setCellValue(Double.parseDouble(bean.getSnjzwap()));

            czbksrRow.createCell(11).setCellValue(Double.parseDouble(bean.getBnsrwap()));

            czbksrRow.createCell(12).setCellValue(Double.parseDouble(bean.getHj2()));

            czbksrRow.createCell(13).setCellValue(Double.parseDouble(bean.getSnjzyszc()));

            czbksrRow.createCell(14).setCellValue(Double.parseDouble(bean.getBnsryszc()));

            czbksrRow.createCell(15).setCellValue(Double.parseDouble(bean.getHj3()));

            czbksrRow.createCell(16).setCellValue(Double.parseDouble(bean.getSnjzyszxl()));

            czbksrRow.createCell(17).setCellValue(Double.parseDouble(bean.getBnsryszxl()));

            czbksrRow.createCell(18).setCellValue(Double.parseDouble(bean.getHj4()));

            thisIndex++;
            List<BudgetSectionIncome2> xj = bean.getXj();
            createZyczbksrXj2(sheet,xj,space+space);

            jssnxj = jssnxj.add(new BigDecimal(bean.getSnjz()));
            jsbnxj = jsbnxj.add(new BigDecimal(bean.getBnsr()));
            jszj = jszj.add(new BigDecimal(bean.getHj()));
            jssnjzysapxj = jssnjzysapxj.add(new BigDecimal(bean.getSnjzysap()));
            jsbnsrysapxj = jsbnsrysapxj.add(new BigDecimal(bean.getBnsrysap()));
            jshj1zj = jshj1zj.add(new BigDecimal(bean.getHj1()));
            jssnjzwapxj = jssnjzwapxj.add(new BigDecimal(bean.getSnjzwap()));
            jsbnsrwapxj = jsbnsrwapxj.add(new BigDecimal(bean.getBnsrwap()));
            jshj2zj = jshj2zj.add(new BigDecimal(bean.getHj2()));
            jssnjzyszcxj = jssnjzyszcxj.add(new BigDecimal(bean.getSnjzyszc()));
            jsbnsryszcxj = jsbnsryszcxj.add(new BigDecimal(bean.getBnsryszc()));
            jshj3zj = jshj3zj.add(new BigDecimal(bean.getHj3()));

            //设置表格中字体
            for (int k = 1; k < 19; k++) {
                if (k == 1)
                    czbksrRow.getCell(k).setCellStyle(fontStyle);
                else
                    czbksrRow.getCell(k).setCellStyle(fontCenterStyle);

            }

        }
    }

    /**
     * 创建财政拨款收入下级
     * @param sheet
     * @param Data
     * @param space
     */
    public void createZyczbksrXj1(HSSFSheet sheet, List<BudgetSectionIncome2> Data, String space){
        if (Data == null){
            return ;
        }
        for (BudgetSectionIncome2 bean:Data) {
            HSSFRow czbksrRow = sheet.createRow(thisIndex);
            czbksrRow.setHeightInPoints(20);

            HSSFCell kmdm = czbksrRow.createCell(1);
            kmdm.setCellValue(bean.getKmmc());
            czbksrRow.createCell(1).setCellValue(space+bean.getKmmc());

            czbksrRow.createCell(2).setCellValue(Double.parseDouble(bean.getSnjz()));
            czbksrRow.createCell(3);
            //合并行上年
            CellRangeAddress regionSn = new CellRangeAddress(thisIndex, thisIndex, 2, 3);
            sheet.addMergedRegion(regionSn);

            czbksrRow.createCell(4).setCellValue(Double.parseDouble(bean.getBnsr()));
            czbksrRow.createCell(5);
            //合并行本年
            CellRangeAddress regionBn = new CellRangeAddress(thisIndex, thisIndex, 4, 5);
            sheet.addMergedRegion(regionBn);


            czbksrRow.createCell(6).setCellValue(Double.parseDouble(bean.getHj()));

            czbksrRow.createCell(7).setCellValue(Double.parseDouble(bean.getSnjzysap()));

            czbksrRow.createCell(8).setCellValue(Double.parseDouble(bean.getBnsrysap()));

            czbksrRow.createCell(9).setCellValue(Double.parseDouble(bean.getHj1()));

            czbksrRow.createCell(10).setCellValue(Double.parseDouble(bean.getSnjzwap()));

            czbksrRow.createCell(11).setCellValue(Double.parseDouble(bean.getBnsrwap()));

            czbksrRow.createCell(12).setCellValue(Double.parseDouble(bean.getHj2()));

            czbksrRow.createCell(13).setCellValue(Double.parseDouble(bean.getSnjzyszc()));

            czbksrRow.createCell(14).setCellValue(Double.parseDouble(bean.getBnsryszc()));

            czbksrRow.createCell(15).setCellValue(Double.parseDouble(bean.getHj3()));

            czbksrRow.createCell(16).setCellValue(Double.parseDouble(bean.getSnjzyszxl()));

            czbksrRow.createCell(17).setCellValue(Double.parseDouble(bean.getBnsryszxl()));

            czbksrRow.createCell(18).setCellValue(Double.parseDouble(bean.getHj4()));

            thisIndex++;
            List<BudgetSectionIncome2> xj = bean.getXj();
            createZyczbksrXj1(sheet,xj,space+space);

            snxj = snxj.add(new BigDecimal(bean.getSnjz()));
            bnxj = bnxj.add(new BigDecimal(bean.getBnsr()));
            zj = zj.add(new BigDecimal(bean.getHj()));
            snjzysapxj = snjzysapxj.add(new BigDecimal(bean.getSnjzysap()));
            bnsrysapxj = bnsrysapxj.add(new BigDecimal(bean.getBnsrysap()));
            hj1zj = hj1zj.add(new BigDecimal(bean.getHj1()));
            snjzwapxj = snjzwapxj.add(new BigDecimal(bean.getSnjzwap()));
            bnsrwapxj = bnsrwapxj.add(new BigDecimal(bean.getBnsrwap()));
            hj2zj = hj2zj.add(new BigDecimal(bean.getHj2()));
            snjzyszcxj = snjzyszcxj.add(new BigDecimal(bean.getSnjzyszc()));
            bnsryszcxj = bnsryszcxj.add(new BigDecimal(bean.getBnsryszc()));
            hj3zj = hj3zj.add(new BigDecimal(bean.getHj3()));

            //设置表格中字体
            for (int k = 1; k < 19; k++) {
                if (k == 1)
                    czbksrRow.getCell(k).setCellStyle(fontStyle);
                else
                    czbksrRow.getCell(k).setCellStyle(fontCenterStyle);

            }
        }
    }

}
